import pandas as pd
import os
import numpy as np
files = os.listdir(r'C:\Users\chumenliuxiang\Desktop\实验室作业\考核数据集\附件1：UWB数据集\正常数据')
#
# for i in range(1):
#     files_name='1.正常.txt'
for files_name in files:#批量定义————————————————————————————————————————————————————————————————————

    df1=pd.read_csv(r'C:\Users\chumenliuxiang\Desktop\实验室作业\考核数据集\附件1：UWB数据集\正常数据'+'\\'+files_name
                   ,sep=':'
                   ,names=['T','时','R',
                           'Tag ID','锚点ID','测距值(mm)',
                           '校验值','序列号','数据号'])
    #————————文件导入命名

    df1=df1.drop(0)#删除第0行
    df1=df1.drop(columns=['T','时','R','Tag ID','校验值','序列号'])#删除无用列
    # print(df1)
    df = pd.read_excel(r'C:\Users\chumenliuxiang\Desktop\实验室作业\模板.xls')#打开模板
    # print(df1)
    # print(df)


    for i in range(int(len(df1)/4)):#数据转移
            df.iloc[i,0] = int(df1.iloc[i*4,1])
            df.iloc[i,1] = int(df1.iloc[i*4+1,1])
            df.iloc[i,2] = int(df1.iloc[i*4+2,1])
            df.iloc[i,3] = int(df1.iloc[i*4+3,1])
            df.iloc[i,4] = df1.iloc[i*4+3,2]



    df= df.drop_duplicates(subset=['01','02','03','04'], keep='first')#删除重复值

    df=df[:-1]


    #——————输出
    import re
    ILLEGAL_CHARACTERS_RE = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]')
    import xlsxwriter
    files_name2=files_name.replace('.txt','.xlsx')
    df.to_excel(r'C:\Users\chumenliuxiang\Desktop\实验室作业\输出\正常数据'+'\\'+files_name2, engine='xlsxwriter')
    print(files_name2)
